package classDAO;

import java.sql.Date;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import mappedClass.Task;
import mappedClass.TaskDailyReporting;
import mappedClass.TaskExpense;
import mappedClass.Team;
import mappedClass.TeamTask;
import mappedClass.TeamTaskId;
import mappedClass.TypeTask;
import mappedClass.User;
import mappedClass.UserTask;
import mappedClass.UserTaskId;
import mappedClass.WinaproachRecord;

import org.hibernate.Query;
import org.hibernate.Session;

import testhibernate.HibernateUtil;

public class TaskDAO implements DAO {

	public List findAll() {
		Session s = HibernateUtil.getSessionFactory().openSession();
		List list = s.createQuery("from Task t").list();
		s.close();
		return list;
	}

	public Object getById(int id) {
		Session s = HibernateUtil.getSessionFactory().openSession();

		return (Task) s.get(Task.class, id);
	}

	public int getIdByName(String name) {
		Session s = HibernateUtil.getSessionFactory().openSession();

		Query q = s
				.createQuery(
						"SELECT id FROM Task t WHERE id IN "
								+ "(SELECT id FROM WinaproachRecord WHERE name= :name) "
								+ "OR "
								+ "t.typetask IN (SELECT id FROM TypeTask WHERE name= :name)")
				.setString("name", name).setString("name", name);

		int id = (Integer) q.uniqueResult();
		return id;
	}

	public HashMap<Integer, Integer> displayPoint(String ssDate, String login) {
		try {
			Session s = HibernateUtil.getSessionFactory().openSession();

			SimpleDateFormat sdf1 = new SimpleDateFormat("dd-MM-yyyy");
			java.util.Date date;

			date = sdf1.parse(ssDate);

			java.sql.Date sqlStartDate = new Date(date.getTime());
			HashMap<Integer, Integer> points = new HashMap<Integer, Integer>();

			Query q = s
					.createQuery(
							"FROM TaskDailyReporting tdr WHERE tdr.currentDate= :date AND userId = (SELECT id FROM User WHERE login= :login)")
					.setString("date", ssDate).setString("login", login);

			List<TaskDailyReporting> list = q.list();

			for (TaskDailyReporting td1 : list) {
				points.put(td1.getUsertask().getTask().getId(), td1.getPoint());
			}

			return points;
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return null;
	}

	/** OK **/
	public HashMap<Integer, Integer> totalPoint(String login,
			ArrayList<Object> list) {

		Session s = HibernateUtil.getSessionFactory().openSession();
		HashMap<Integer, Integer> totalPoints = new HashMap<Integer, Integer>();

		Query q = s
				.createQuery("SELECT tdr.usertask.task.id, SUM(point) AS sum "
						+ "FROM TaskDailyReporting tdr "
						+ "GROUP BY tdr.usertask.task.id");
		List<Object[]> rows = q.list();

		for (Object[] row : rows) {
			System.out.println(" ------------------- ");
			System.out.println("id: " + row[0]);
			System.out.println("total point: " + row[1]);
			totalPoints.put(((Integer) row[0]).intValue(),
					((Long) row[1]).intValue());
		}

		return totalPoints;
	}

	/** OK **/
	public List<Object[]> taskByTeam(String name) {

		Session s = HibernateUtil.getSessionFactory().openSession();
		TeamDAO teamDAO = new TeamDAO();
		TypeTaskDAO typeTasDAO = new TypeTaskDAO();

		System.out.println("ID TEAM : " + teamDAO.getIdByName(name));

		// We take all CR task
		Query q = s
				.createQuery(
						"SELECT tex, t "
								+ "FROM "
								+ "Task t, TaskExpense tex "
								+ "WHERE "
								+ "t.id IN (SELECT tt.task FROM TeamTask tt WHERE teamId = :team) "
								+ "AND "
								+ "tex.task = t.id "
								+ "AND "
								+ "currentDate = (SELECT max(currentDate) FROM TaskExpense te WHERE task = t.id)")
				.setInteger("team", teamDAO.getIdByName(name));

		List<Object[]> list = q.list();

		return list;
	}

	/** OK **/
	public List<TaskExpense> workedTask(String login) {

		Session s = HibernateUtil.getSessionFactory().openSession();
		UserDAO userDAO = new UserDAO();
		TypeTaskDAO typeTasDAO = new TypeTaskDAO();

		System.out.println("ID USER : " + userDAO.getIdByName(login));

		// We take all CR task
		Query q = s
				.createQuery(
						"SELECT tex "
								+ "FROM "
								+ "Task t, TaskExpense tex "
								+ "WHERE "
								+ "t.id IN (SELECT ut.task FROM UserTask ut WHERE userId = :userid) "
								+ "AND "
								+ "tex.task = t.id "
								+ "AND "
								+ "currentDate = (SELECT max(currentDate) FROM TaskExpense te WHERE task = t.id)")
				.setInteger("userid", userDAO.getIdByName(login));

		List<TaskExpense> list = q.list();
		for (TaskExpense row : list) {
			System.out.println(row.getClass());
			System.out.println(row.getCompletionTime());
			System.out.println(row.getTask().getDescription());
		}

		return null;
	}

	public boolean addTask(String typetask, String desc) {

		Session s = HibernateUtil.getSessionFactory().openSession();
		TypeTaskDAO typeTasDAO = new TypeTaskDAO();

		int idTypeTask = typeTasDAO.getIdByName(typetask);

		Task newTask = new Task((TypeTask) typeTasDAO.getById(idTypeTask), desc);

		s.beginTransaction();

		s.save(newTask);

		s.getTransaction().commit();
		s.close();

		return true;
	}

	/** OK **/
	/**
	 * We add a new winaproach record, so we create also the task
	 * 
	 * @param recordNumber
	 * @param nameWinapR
	 * @param typetask
	 * @param desc
	 * @return
	 */
	public boolean addWinaproachRecord(int recordNumber, String nameWinapR,
			String typetask, String desc) {

		Session s = HibernateUtil.getSessionFactory().openSession();
		TypeTaskDAO typeTasDAO = new TypeTaskDAO();

		int idTypeTask = typeTasDAO.getIdByName(typetask);

		WinaproachRecord newWinaprRecord = new WinaproachRecord(
				(TypeTask) typeTasDAO.getById(idTypeTask), desc, nameWinapR,
				recordNumber);

		s.beginTransaction();

		s.save(newWinaprRecord);

		s.getTransaction().commit();
		s.close();

		return true;
	}

	/** OK **/
	public boolean addUserTask(int recordNumber, String nameWinapR,
			String ssDate, String login) {
		try {

			/** HERE WE CREATE THE DATE AS THE RIGHT FORMAT **/
			SimpleDateFormat sdf1 = new SimpleDateFormat("dd-MM-yyyy");
			ssDate = ssDate.replace("<big>", "");
			ssDate = ssDate.replace("</big>", "");
			java.util.Date date;
			date = sdf1.parse(ssDate);
			java.sql.Date sqlStartDate = new Date(date.getTime());

			/** WE OPEN THE SESSION **/
			Session s = HibernateUtil.getSessionFactory().openSession();

			/** WE CREATE ALL USEFUL DAO **/
			UserDAO userDAO = new UserDAO();
			WinaproachDAO winaproachDAO = new WinaproachDAO();
			TaskDAO taskDAO = new TaskDAO();
			UserTeamHistoryDAO uthDAO = new UserTeamHistoryDAO();
			TeamDAO teamDAO = new TeamDAO();

			/** WE SAVE THE USEFUL IDs **/
			int idWinapr = winaproachDAO.getIdByName(nameWinapR);
			int idUser = userDAO.getIdByName(login);
			int teamId = uthDAO.getIdByUserId(idUser);

			/** WE START THE TRANSACTION **/
			s.beginTransaction();

			// We assign the task to the user
			UserTaskId newUTId = new UserTaskId(idWinapr, idUser);
			UserTask newUT = new UserTask(newUTId,
					(Task) taskDAO.getById(idWinapr),
					(User) userDAO.getById(idUser));

			s.save(newUT);

			// We assign the task to the team
			TeamTaskId newTTId = new TeamTaskId(idWinapr, teamId);
			TeamTask newTT = new TeamTask(newTTId,
					(Task) taskDAO.getById(idWinapr),
					(Team) teamDAO.getById(teamId));

			s.save(newTT);

			// We assign the task to the TaskExpense
			TaskExpense newTE = new TaskExpense(
					(Task) taskDAO.getById(idWinapr), 0, sqlStartDate);

			s.save(newTE);

			/** COMMIT AND CLOSE **/
			s.getTransaction().commit();
			s.close();

			return true;

		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return false;
	}

	/** OK **/
	public boolean completionTime(String ssDate, String nameWinapR,
			int completionTime) {

		try {
			/** HERE WE CREATE THE DATE AS THE RIGHT FORMAT **/
			SimpleDateFormat sdf1 = new SimpleDateFormat("dd-MM-yyyy");
			ssDate = ssDate.replace("<big>", "");
			ssDate = ssDate.replace("</big>", "");
			java.util.Date date;
			date = sdf1.parse(ssDate);
			java.sql.Date sqlStartDate = new Date(date.getTime());

			/** WE OPEN THE SESSION **/
			Session s = HibernateUtil.getSessionFactory().openSession();
			WinaproachDAO winaproachDAO = new WinaproachDAO();
			TaskDAO taskDAO = new TaskDAO();

			int idWinapr = winaproachDAO.getIdByName(nameWinapR);

			/** WE START THE TRANSACTION **/
			s.beginTransaction();

			Query q = s
					.createQuery(
							"FROM TaskExpense te WHERE "
									+ "te.currentDate= :date AND te.task= :idtask")
					.setDate("date", sqlStartDate)
					.setInteger("idtask", idWinapr);

			if (q.uniqueResult() != null) {
				/** WE UPDATE THE completionTime **/
				TaskExpense oldTE = (TaskExpense) q.uniqueResult();
				oldTE.setCompletionTime(completionTime);
				s.update(oldTE);
			}

			else {
				/** WE CREATE THE TASKEXPENSE **/
				TaskExpense newTE = new TaskExpense(
						(WinaproachRecord) winaproachDAO.getById(idWinapr),
						completionTime, sqlStartDate);

				s.save(newTE);
			}

			/** COMMIT AND CLOSE **/
			s.getTransaction().commit();
			s.close();

		} catch (ParseException ex) {
			Logger.getLogger(TaskDAO.class.getName()).log(Level.SEVERE, null,
					ex);
		}

		return true;
	}

	/** OK **/
	/**
	 * WE GET ALL INFORMATION OF TASK AND TEAM (DEPARTMENT MANAGER)
	 * 
	 * @return List<Object[]>
	 */
	public List<Object[]> informationTaskTeam() {

		Session s = HibernateUtil.getSessionFactory().openSession();

		Query q = s
				.createQuery("SELECT t, te, tex FROM Task t, Team te, User u, TaskExpense tex "
						+ "WHERE "
						+ "t.id IN (SELECT tt.task FROM TeamTask tt WHERE te.id = teamId) "
						+ "AND "
						+ "te.user = u.id "
						+ "AND "
						+ "tex.task = t.id "
						+ "AND "
						+ "tex.currentDate = (SELECT max(currentDate) FROM TaskExpense te WHERE task = t.id) "
						+ "ORDER BY te.name");

		List<Object[]> list = q.list();

		return list;
	}

	public List<Object> terminatedTask() {

		Session s = HibernateUtil.getSessionFactory().openSession();

		Query q = s
				.createQuery("SELECT t FROM Task t, TaskExpense tex "
						+ "WHERE "
						+ "tex.task = t.id "
						+ "AND "
						+ "tex.currentDate = (SELECT max(currentDate) FROM TaskExpense te WHERE task = t.id) "
						+ "AND " + "tex.completionTime = 100");

		List<Object> list = q.list();

		return list;

	}

	/** OK **/
	public int timeSpentByDay(String ssDate, String login) {

		int sumTime = 0;
		Session s = HibernateUtil.getSessionFactory().openSession();

		try {
			SimpleDateFormat sdf1 = new SimpleDateFormat("dd-MM-yyyy");
			java.util.Date date = sdf1.parse(ssDate);
			java.sql.Date sqlStartDate = new Date(date.getTime());

			Query q = s
					.createQuery(
							"SELECT SUM(point) FROM TaskDailyReporting tdr "
									+ "WHERE "
									+ "tdr.usertask.user = (SELECT id FROM User WHERE login = :login) "
									+ "AND " + "tdr.currentDate = :date ")
					.setString("login", login).setDate("date", sqlStartDate);

			sumTime = ((Long) q.uniqueResult()).intValue();

			return sumTime;

		} catch (ParseException ex) {
			Logger.getLogger(TaskDAO.class.getName()).log(Level.SEVERE, null,
					ex);
		}

		return 0;
	}

}

//
// public boolean insertPointTask(int point, int currow, String ssDate, String
// login) throws SQLException {
// // TODO Auto-generated method stub
// try {
// // We get the current day
// SimpleDateFormat sdf1 = new SimpleDateFormat("dd-MM-yyyy");
// ssDate = ssDate.replace("<big>", "");
// ssDate = ssDate.replace("</big>", "");
// java.util.Date date = sdf1.parse(ssDate);
// java.sql.Date sqlStartDate = new Date(date.getTime());
//
// PreparedStatement s = (PreparedStatement) conn.prepareStatement(
// "SELECT point FROM linktaskpoint WHERE "
// + "currDate = ? AND "
// + "idUser = (select id from user where login = ?) AND "
// + "idTask = ?"
// );
// s.setDate(1, sqlStartDate);
// s.setString(2, UserManager.getInstance().getUser(login).getLogin());
// s.setInt(3, currow);
// ResultSet r = s.executeQuery();
//
// if (r.first()) {
// // We delete all information of the current day
// PreparedStatement stmt = (PreparedStatement) conn.prepareStatement(
// "UPDATE linktaskpoint SET linktaskpoint.point = ? "
// + "WHERE currDate = ? AND "
// + "idUser = (select id from user where login = ?) AND "
// + "idTask = ?"
// );
// stmt.setInt(1, point);
// stmt.setDate(2, sqlStartDate);
// stmt.setString(3, UserManager.getInstance().getUser(login).getLogin());
// stmt.setInt(4, currow);
// stmt.executeUpdate();
//
// conn.commit();
// } else {
// // We add the task in the userHasTask table
// if (point > 0) {
// PreparedStatement stmt1 = (PreparedStatement) conn.prepareStatement(
// "INSERT INTO linktaskpoint VALUES ("
// + "?,"
// + "?,"
// + "(select id from user where login = ?),"
// + "?"
// + ")"
// );
//
// stmt1.setDate(1, sqlStartDate);
// stmt1.setInt(2, point);
// stmt1.setString(3, UserManager.getInstance().getUser(login).getLogin());
// stmt1.setInt(4, currow);
// stmt1.executeUpdate();
//
// conn.commit();
// }
// }
//
// return true;
// } catch (SQLException e) {
// e.printStackTrace(System.err);
// System.err.println("SQLState: "
// + ((SQLException) e).getSQLState());
//
// System.err.println("Error Code: "
// + ((SQLException) e).getErrorCode());
//
// System.err.println("Message: " + e.getMessage());
// } catch (ParseException ex) {
// Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
// }
// return false;
// }

//
// public CR autoCompleteCR(String name) throws SQLException {
// PreparedStatement stmt;
// stmt = (PreparedStatement) conn.prepareStatement(
// "SELECT * FROM( "
// + "SELECT "
// +
// "DISTINCT(cr.id), cr.idTask, typeCR, description, progressPercent, currDate "
// + "FROM "
// + "cr, task, linktaskexpense "
// + "WHERE "
// +
// "cr.idTask IN (SELECT idTask FROM userHasTask WHERE idUser = (SELECT id from user WHERE login = ?)) "
// + "AND "
// + "cr.idTask = task.id "
// + "AND "
// + "linktaskexpense.idTask = task.id "
// + "ORDER BY "
// + "currDate DESC) as sel "
// + "GROUP BY "
// + "sel.idTask "
// );
// stmt.setString(1, name);
// System.out.println(stmt.asSql());
// ResultSet r = stmt.executeQuery();
// //while (r.next()) {
// r.next();
// int id = r.getInt("cr.id");
// String nameCR = r.getString("typeCR");
// int idTask = r.getInt("idTask");
// String desc = r.getString("description");
// int completion = r.getInt("progressPercent");
// System.out.println("Val CR : " + id + "  " + nameCR + "  " + idTask + "  " +
// desc + "  " + completion);
// //}
// return new CR(idTask, nameCR, id, desc, completion);
// }

//
// public String getLastDate () throws SQLException {
// String date = null;
// PreparedStatement s = (PreparedStatement) conn.prepareStatement(
// "SELECT MAX(currDate) as date FROM linktaskexpense"
// );
// ResultSet r = s.executeQuery();
// while (r.next()) {
// date = r.getString("date");
// }
// return date;
// }

//
// public boolean checkTaskName(String name) throws SQLException {
// PreparedStatement s = (PreparedStatement) conn.prepareStatement(
// "SELECT * FROM cr WHERE "
// + "typeCR = ? "
// );
// s.setString(1, name);
// ResultSet r = s.executeQuery();
//
// return r.first();
// }

//
// public boolean checkTaskNumber(int number) throws SQLException {
// PreparedStatement s = (PreparedStatement) conn.prepareStatement(
// "SELECT * FROM cr WHERE "
// + "id = ? "
// );
// s.setInt(1, number);
// ResultSet r = s.executeQuery();
//
// return r.first();
// }
